package domain.web.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import domain.web.dao.CategoryDao;
import domain.web.model.Category;
import domain.web.model.CategoryF;

@Repository("categoryDao")
public class CategoryDaoImpl extends JdbcDaoSupport implements CategoryDao {

	@Autowired
	private DataSource dataSource;

	@PostConstruct
	void init() {
		setDataSource(dataSource);
	}

	@Override
	public long insert(final Category category) {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		final PreparedStatementCreator psc = new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement pstmt = con.prepareStatement(
						"INSERT INTO Categories(Name_vi, Name_en, ParentId, Uri) "
								+ "VALUES(?,?,?,?)",
						Statement.RETURN_GENERATED_KEYS);
				pstmt.setString(1, category.getName_vi());
				pstmt.setString(2, category.getName_en());
				if (category.getParentId() == 0) {
					pstmt.setNull(3, java.sql.Types.INTEGER);
				} else {
					pstmt.setLong(3, category.getParentId());
				}
				pstmt.setString(4, category.getUri());
				return pstmt;
			}
		};
		getJdbcTemplate().update(psc, keyHolder);
		return keyHolder.getKey().longValue();
	}

	@Override
	public void update(Category category) {
		String sql = "UPDATE Categories SET Name_vi = ?, Name_en = ?, ParentId = ?, Uri = ? WHERE Id = ?";
		getJdbcTemplate().update(sql, category.getName_vi(),
				category.getName_en(), category.getParentId(),
				category.getUri(), category.getId());
	}

	@Override
	public void delete(long id) {
		String sql = "DELETE FROM Categories WHERE Id = ?";
		getJdbcTemplate().update(sql, id);
	}

	@Override
	public Category get(long id, String locale) {
		String sql = "SELECT Id, Name_" + locale
				+ " AS Name, ParentId, Uri FROM Categories WHERE Id = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				id);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}

	@Override
	public Category get(String uri, String locale) {
		String sql = "SELECT Id, Name_" + locale
				+ " AS Name, ParentId, Uri FROM Categories WHERE Uri = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				uri);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}

	@Override
	public List<Category> getSubList(long id, String locale) {
		String sql = "SELECT Id, Name_" + locale
				+ " AS Name, ParentId, Uri FROM Categories WHERE ParentId = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				id);
		List<Category> categories = new ArrayList<Category>();
		for (Map<String, Object> map : results) {
			categories.add(rowMapper(map));
		}
		return categories;
	}

	@Override
	public List<Category> getTopList(String locale) {
		String sql = "SELECT Id, Name_"
				+ locale
				+ " AS Name, ParentId, Uri FROM Categories WHERE ParentId is NULL";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql);
		List<Category> categories = new ArrayList<Category>();
		for (Map<String, Object> map : results) {
			categories.add(rowMapper(map));
		}
		return categories;
	}

	@Override
	public List<CategoryF> getDepthCategory(String locale) {
		String sql = "SELECT Id, Name_"
				+ locale
				+ " AS Name, ParentId, Uri FROM Categories WHERE ParentId is NULL";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql);
		List<CategoryF> categories = new ArrayList<CategoryF>();
		for (Map<String, Object> map : results) {
			CategoryF categoryF = rowMapperF(map);
			categories.add(categoryF);
			recursiveInCatTree(categoryF, locale);
		}

		return categories;
	}

	@Override
	public List<Category> getBreadcrumbsCategories(long id, String locale) {
		String sql = "SELECT Id, Name_" + locale
				+ " AS Name, ParentId, Uri FROM Categories WHERE Id= ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				id);
		List<Category> categories = new ArrayList<Category>();
		for (Map<String, Object> map : results) {
			Category category = rowMapper(map);
			categories.add(category);
			recursiveBreadcrumbs(categories, category, locale);
		}
		return categories;

	}

	private void recursiveInCatTree(CategoryF parent, String locale) {
		parent.setChildren(new ArrayList<CategoryF>());

		List<Category> categories = getSubList(parent.getId(), locale);

		for (Category category : categories) {
			CategoryF categoryF = convert(category);
			parent.getChildren().add(categoryF);
			recursiveInCatTree(categoryF, locale);
		}
	}

	private void recursiveBreadcrumbs(List<Category> categories,
			Category category, String locale) {
		if (category.getParentId() > 0) {
			Category c = get(category.getParentId(), locale);
			categories.add(c);
			recursiveBreadcrumbs(categories, c, locale);
		}
	}

	private Category rowMapper(Map<String, Object> row) {
		Category category = new Category();
		category.setId((Long) row.get("Id"));
		category.setName((String) row.get("Name"));
		if (row.get("ParentId") != null)
			category.setParentId((Long) row.get("ParentId"));
		category.setUri((String) row.get("Uri"));
		return category;
	}

	private CategoryF rowMapperF(Map<String, Object> row) {
		CategoryF category = new CategoryF();
		category.setId((Long) row.get("Id"));
		category.setName((String) row.get("Name"));
		category.setUri((String) row.get("Uri"));
		return category;
	}

	private CategoryF convert(Category category) {
		CategoryF categoryF = new CategoryF();
		categoryF.setId(category.getId());
		categoryF.setName(category.getName());
		categoryF.setUri(category.getUri());

		return categoryF;
	}

}
